﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using SapB1WebPOS.Utils;

namespace SapB1WebPOS.DAL
{
    public class ARInvoiceDAO
    {
        public DataSet GetOINV(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.AR_INVOICE_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                StringBuilder query = new StringBuilder();
                query.Append("SELECT  SlpCode,DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,DocDate,DocDueDate,TaxDate,VatSum FROM ");
                query.Append(TableNameDefined.AR_INVOICE_HEADER);
                query.Append(" WHERE CANCELED <> 'C'");
                query.Append(" ORDER BY DocEntry DESC ");
                dt = connect.Excute_Query(query.ToString(), null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetAllPayment(string companyDB, string docEntry)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.AR_INVOICE_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(docEntry);
                StringBuilder query = new StringBuilder();

                query.Append("  SELECT * FROM ( SELECT * FROM ( SELECT T1.DocEntry,  'CASH' PaymentType, 'CASH' PaymentCode, 'CASH' PaymentName,T0.CashSum Amount, '0' AcctCode");
                query.Append(" FROM ORCT T0 JOIN RCT2  T1 ON T0.DocEntry  = T1.DocNum ) T0");
                query.Append(" WHERE Amount<>0");
                query.Append(" UNION ALL ");
                query.Append(" SELECT * FROM ( SELECT  T1.DocEntry, 'BT' PaymentType, 'BT' PaymentCode, 'BANK TRANSFER' PaymentName,  T0.TrsfrSum  Amount,'0' AcctCode");
                query.Append(" FROM ORCT T0 JOIN RCT2  T1 ON T0.DocEntry  = T1.DocNum ) T0 ");
                query.Append(" WHERE Amount<>0");
                query.Append(" UNION ALL ");
                query.Append(" SELECT T2.DocEntry DocEntry,  'CC' PaymentType,convert(nvarchar(100),T0.CreditCard) PaymentCode,CardName  PaymentName,ISNULL(CreditSum,0) Amount,AcctCode FROM OCRC T0");
                query.Append(" LEFT JOIN RCT3 T1  ON T0.CreditCard = T1.CreditCard ");
                query.Append(" LEFT JOIN  RCT2 T2 ON T1.DocNum  = T2.DocNum) T0 ");
                query.Append(" WHERE DocEntry = @Param1");

                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetPayment(string companyDB, string docEntry)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.AR_INVOICE_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(docEntry);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT T0.SlpCode, T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.AR_INVOICE_HEADER + " T0 JOIN " + TableNameDefined.AR_INVOICE_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  WHERE T0.DocEntry = @Param1 AND T0.CANCELED <> 'C'");
                query.Append("  GROUP BY T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry ASC  ");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOINVByDocEntry(string companyDB, string docEntry)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.AR_INVOICE_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(docEntry);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT  T1.SlpCode,T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.AR_INVOICE_HEADER + " T0 JOIN " + TableNameDefined.AR_INVOICE_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  WHERE T0.DocEntry = @Param1 AND T0.CANCELED <> 'C'");
                query.Append("  GROUP BY T1.SlpCode,T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry ASC  ");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOINVForSearch(string companyDB, string keySearch)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.AR_INVOICE_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(keySearch);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT  T0.SlpCode,T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.AR_INVOICE_HEADER + " T0 JOIN " + TableNameDefined.AR_INVOICE_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  WHERE T0.DocEntry LIKE '%' + @Param1 + '%' AND T0.CANCELED <> 'C'");
                query.Append("  GROUP BY T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry ASC  ");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOINVLastRecord(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.AR_INVOICE_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT TOP 1  T0.SlpCode,T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.AR_INVOICE_HEADER + " T0 JOIN " + TableNameDefined.AR_INVOICE_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  WHERE T0.CANCELED <> 'C'");
                query.Append("  GROUP BY T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry DESC ");

                dt = connect.Excute_Query(query.ToString(), null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }

        public DataSet GetINV1ByDocEntry(string companyDB, string docEntry)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.AR_INVOICE_LINE);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(docEntry);
                StringBuilder query = new StringBuilder();
                query.Append("SELECT  T0.SlpCode,OWHS.WhsName,ItemCode ,Dscription ,PriceBefDi ,DiscPrcnt ,Price ,unitMsr, Quantity,  LineTotal, T0.VatGroup, VatPrcnt,T0.OpenQty ");
                query.Append(" ,VatSum ,T0.WhsCode ,DocEntry , LineNum ,LineStatus,OVTG.Name AS VatGroupName  ");
                query.Append(" FROM " + TableNameDefined.AR_INVOICE_LINE + " T0 ");
                query.Append(" JOIN OWHS ON T0.WhsCode = OWHS.WhsCode ");
                query.Append(" JOIN OVTG ON OVTG.Code = T0.VatGroup ");
                query.Append(" WHERE DocEntry = @Param1");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
    }
}